package com.hfzy.ihk.common.util.file.excel;


import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    static SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static SimpleDateFormat format2 = new SimpleDateFormat("HH:mm:ss");
    /***
     * 根据文件流获取Workbook 工作薄
     * @param inp
     * @return
     */
    public static Workbook getExcelWorkbook(InputStream inp) {
        Workbook wb = null;
        try{
            wb = WorkbookFactory.create(inp);
            wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
        }catch(Exception ex){
            ;
        }finally{
            if(inp != null){
                try {
                    inp.close();
                }catch(IOException e){
                    ;
                }
            }
        }
        return wb;
    }

    /**
     * 根据Workbook工作薄获取工作表
     * @param workbook
     * @return
     */
    public static List<Sheet> getSheetListByWorkbook(Workbook workbook) {
        List<Sheet> sheetList = new ArrayList<Sheet>();
        int sheetSize = workbook.getNumberOfSheets();
        for(int i=0;i<sheetSize;i++){
            try {
                sheetList.add(workbook.getSheetAt(i));
            } catch (Exception e) {
                ;
            }
        }
        return sheetList;
    }


    /***
     * 根据工作表sheet获取该工作表的所有行
     * @param sheet
     * @return
     */
    public static List<Row> getRowListBySheet(Sheet sheet) {
        List<Row> rows = new ArrayList<Row>();
        Iterator<Row> iterator =  sheet.rowIterator();
        while (iterator.hasNext()){
            rows.add(iterator.next());
        }
        return rows;
    }


    /***
     * 根据工作表sheet获取该工作表的第一行的列数
     * @param sheet
     * @return
     */
    public static int getFirstRowCellCount(Sheet sheet) {
        if(sheet.getLastRowNum() == 0)
            return 0;
        return sheet.getRow(0).getLastCellNum();
    }

    /***
     * 根据行号，列号获取该行的所有列数据
     * @param row
     * @param cellNum
     * @return
     */
    public static List<Cell> getCellListByRow(Row row, int cellNum) {
        --cellNum;
        List<Cell> cellList = new  ArrayList<Cell>();
        if(cellNum >= 0){
            for(int i =0;i<=cellNum;i++){
                cellList.add(row.getCell(i));
            }
        }
        return cellList;
    }



    /**
     * 根据该列的数据类型获取该列的值
     * @param cell
     * @return
     */
    public static Object getValueByCell(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return cell.getBooleanCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
            return cell.getErrorCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                SimpleDateFormat sdf = null;
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                        .getBuiltinFormat("h:mm")) {
                    sdf = new SimpleDateFormat("HH:mm:ss");
                } else {// 日期
                    sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                }
                Date date = cell.getDateCellValue();
                return sdf.format(date);
            }
            return cell.getNumericCellValue();
        } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else {
            return cell.getStringCellValue();
        }
    }


    /**
     * 从输入流中读取excel文件数据（调用前请保证inp中的文件是excel文件）
     * @param inp
     * @return
     */
    public static List<Map> getDataFromInputStream(InputStream inp){
        List<Map> wrokBookReadData = new ArrayList<Map>();
        try{
            if(null==inp){
                throw new Exception("传入的流不存在!");
            }

            Workbook workBook = ExcelUtils.getExcelWorkbook(inp);
            List<Sheet> sheets = ExcelUtils.getSheetListByWorkbook(workBook);
            for(Sheet sheet:sheets){
                List<Row> rowList = ExcelUtils.getRowListBySheet(sheet);//行集合
                int colCount = ExcelUtils.getFirstRowCellCount(sheet); //第一行的总列数
                List<Map> sheeetDataList = new ArrayList<Map>();
                if(rowList != null && !rowList.isEmpty()){
                    List<Cell> headTitleVal = ExcelUtils.getCellListByRow(rowList.get(0),colCount);
                    rowList.remove(0);//去除第一行（文字标题行）
                    int rowIndex = 2;
                    for(Row row:rowList){
                        int i=0;
                        Map oCellVal = new HashMap();
                        List<Cell> cellList = ExcelUtils.getCellListByRow(row, colCount);
                        for(Cell cell:cellList){
                            oCellVal.put(headTitleVal.get(i++),ExcelUtils.getValueByCell(cell));
                        }
                        oCellVal.put("rowIndex",rowIndex++);
                        sheeetDataList.add(oCellVal);
                    }
                }
                Map map = new HashMap();
                map.put("sheetName",sheet.getSheetName());
                map.put("'sheetValue'",sheeetDataList);
                wrokBookReadData.add(map);
            }
        }catch(Exception ex){
            ;
        }finally{
            if(inp != null){
                try {
                    inp.close();
                }catch(IOException e){
                    ;
                }
            }
        }
        return wrokBookReadData;
    }



    /**
     * 生成Excel
     * @param sheetName
     * @param data List<Map>
     *             如果为空列表，则生成的表中仅显示'没有数据'
     *             若不为空列表，则data[0].keySet()将自动作为表头
     * @return
     */
    public static Workbook generate(String sheetName, List<Map> data) {
        Workbook workbook = new HSSFWorkbook();
        addSheet(workbook, sheetName, data);
        return workbook;
    }

    /**
     * 生成Excel
     * @param sheetName
     * @param data
     * @param columns Map，key为属性名，value为title
     * @return
     */
    public static Workbook generate(String sheetName, Map columns, List data) {
        Workbook workbook = new HSSFWorkbook();
        addSheet(workbook, sheetName, columns, data);
        return workbook;
    }

    public static Workbook generateErrMsgs(List<String> errMsgs){
        Map header = new HashMap();
        header.put("errMsg","错误信息");
        List<Map> maps = new ArrayList<Map>();
        for(String str:errMsgs){
            Map map = new HashMap();
            map.put("errMsg",str);
            maps.add(map);
        }
        Workbook workbook = generate("错误信息",header,maps);
        return workbook;
    }

    /**
     * 在Workbook中添加一个Sheet
     * @param workbook
     * @param sheetName
     * @param columns
     * @param data
     */
    public static Sheet addSheet(Workbook workbook, String sheetName, Map columns, List<Map> data) {
        Sheet sheet = workbook.createSheet(sheetName);

        List<String> columnNames = new ArrayList<String>(columns.values());
        List<String> columnTitles = new ArrayList<String>(columns.keySet());

        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing patriarch = sheet.createDrawingPatriarch();

        //header
        addRow(sheet, 0, columnTitles,patriarch);

        //data
        for(Map map :data){
            List values = new ArrayList();
            for(int i=0;i<columnTitles.size();i++){
                values.add(map.get(columnTitles.get(i)));
            }
            appendRow(sheet, values, patriarch);
        }
        return sheet;
    }


    /**
     * 在Workbook中添加一个Sheet
     * @param workbook
     * @param sheetName
     * @param data List<Map>
     *             如果为空列表，则生成的表中仅显示'没有数据'
     *             若不为空列表，则data[0].keySet()将自动作为表头
     */
    public static Sheet addSheet(Workbook workbook, String sheetName, List<Map> data) {
        Sheet sheet = workbook.createSheet(sheetName);

        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        Drawing patriarch = sheet.createDrawingPatriarch();

        if(data==null || data.isEmpty()) {
            List list = new ArrayList<String>();
            list.add("没有数据");
            addRow(sheet, 0, list, patriarch);
        }
        else {
            //header
            List columnTitles = new ArrayList<String>(data.get(0).keySet());
            addRow(sheet, 0, columnTitles, patriarch);

            //data
            for(Map map :data){
                List values = new ArrayList();
                for(int i=0;i<columnTitles.size();i++){
                    values.add(map.get(columnTitles.get(i)));
                }
                appendRow(sheet, values, patriarch);
            }
        }

        return sheet;
    }


    public static Row addRow(Sheet sheet, int rowNum, List<String> data,Drawing patriarch) {
        Row row = sheet.createRow(rowNum);
        for(int idx=0;idx<data.size();idx++){
            Cell cell = row.createCell(idx);
            cell.setCellValue(data.get(idx));
        }
        return row;
    }

    public static Row appendRow(Sheet sheet, List data,Drawing patriarch) {
        return addRow(sheet, sheet.getLastRowNum() + 1, data,patriarch);
    }

    /***
     * 读取excel文件的数据
     * @param filePath
     * @return
     */
    public static List<Map> getDateFromExcelNew(String filePath){
        List<Map> wrokBookReadData = new ArrayList<Map>();
        FileInputStream inp = null;
        try{
            File file = new File(filePath);
            if(!file.exists() && !file.isFile()){
                throw new Exception("文件不存在!");
            }

            inp = new FileInputStream(file);
            Workbook workBook = getExcelWorkbook(inp);
            List<Sheet> sheets = getSheetListByWorkbook(workBook);
            for(Sheet sheet:sheets){
                List<Row> rowList = getRowListBySheet(sheet);	//行集合
                short colCount = rowList.get(0).getLastCellNum(); //第一行的总列数
                List sheeetDataList = new ArrayList();
                if(rowList != null && !rowList.isEmpty()){
                    List<Cell> headTitleVal = getCellListByRow(rowList.get(0),colCount);
                    rowList.remove(0);//去除第一行（文字标题行）
                    int rowIndex = 2;
                    for(Row row:rowList){
                        int i=0;
                        Map oCellVal = new HashMap();
                        List<Cell> cellList = getCellListByRow(row, colCount);
                        for(Cell cell:cellList){
                            oCellVal.put(headTitleVal.get(i++),getValueByCell(cell));
                        }
                        oCellVal.put("rowIndex",rowIndex++);
                        sheeetDataList.add(oCellVal);
                    }
                }
                Map map = new HashMap();
                map.put("sheetName",sheet.getSheetName());
                map.put("sheetValue",sheeetDataList);
                wrokBookReadData.add(map);
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }finally{
            if(inp != null){
                try {
                    inp.close();
                }catch(IOException e){
                    ;
                }
            }
        }
        return wrokBookReadData;
    }

    public static void outFile(HSSFWorkbook workbook,String path) {
        OutputStream os=null;
        try {
            os = new FileOutputStream(new File(path));
            workbook.write(os);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) throws Exception {
        System.out.println("开始读取文件....");
		String filePath = "F:\\DOC\\联动call客.xlsx";
		if(filePath.trim().indexOf(".xls") == -1 && filePath.trim().indexOf(".XLS") == -1) {
			throw new Exception("文件格式不正确!");
		}

		List<Map> list = ExcelUtils.getDateFromExcelNew(filePath);
		for(Map map : list){
            System.out.println("sheetName"+map.get("sheetName")+",sheetValue"+map.get("sheetValue"));
        }
        System.out.println("读取文件结束...");

        FileInputStream fileInputStream = new FileInputStream("F:\\DOC\\联动call客.xlsx");
        Workbook workbook =  ExcelUtils.getExcelWorkbook(fileInputStream);
        List<Sheet> sheetList= ExcelUtils.getSheetListByWorkbook(workbook);
        List<Map> phoneList = new ArrayList<Map>();
        for(Sheet sheet:sheetList){
            List<Row> rowList = ExcelUtils.getRowListBySheet(sheet);
            for(Row row:rowList){
                Cell nameCell = row.getCell(0);
                Cell phoneCell = row.getCell(1);
                nameCell.setCellType(1);
                phoneCell.setCellType(1);

                Map phoneMap = new HashMap();
                phoneMap.put("customerName",nameCell.getStringCellValue());
                phoneMap.put("customerPhone",phoneCell.getStringCellValue());
                phoneList.add(phoneMap);
            }
        }
        for(Map map : phoneList){
            System.out.println(map.get("customerName")+":"+map.get("customerPhone"));
        }
    }


}
